﻿using CSharp.Libs.Database;
using CSharp.Libs.WPF.UserControls.SmartMediaPlayer.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace CSharp.Libs.WPF.UserControls.SmartMediaPlayer
{
	/// <summary>
	/// data access layer pattern for all play list things
	/// </summary>
	/// <remarks>TODO build some database administration and caring functions (i.e. if tblLastPlayed contains more than one entry, delete the rest)</remarks>
    class DataAccessLayer : IDisposable
    {
        #region declarations
        private SQLiteHandler _sh;
        private ObservableCollection<PlaylistItem> _ocPlaylistItems = new ObservableCollection<PlaylistItem>();
        #endregion



        #region constructor
        public DataAccessLayer()
        {
            //Storage = new DataAccessLayer(new SQLiteHandler(string.Concat(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "\\FileTagger.s3db")))
            _sh = new SQLiteHandler(string.Concat(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "\\SmartMediaPlayer.s3db"));

            _readPlaylist();
        }

        #region dispose pattern (http://www.codeproject.com/Articles/15360/Implementing-IDisposable-and-the-Dispose-Pattern-P)
        // some fields that require cleanup
        private bool disposed = false; // to detect redundant calls

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    // clean up managed handles
                    _sh.Dispose();
                }

                disposed = true;
            }
        }

        public void Dispose()
        {
            Dispose(true);
        }
        #endregion
        #endregion



        #region private functions
        private void _addFilenamesToPlaylist(
            string[] Filenames)
        {
			foreach (string strFilename in Filenames)
			{
				_addFilenameToPlaylist(strFilename);
			}
        }
		private void _addFilenameToPlaylist(
			string Filename)
		{
			if (!_filenameExists(Filename))
			{
				//get new SortOrder value
				long lngNewSortOrder = _getNewSortOrder();

				//declare string to hold the prepared sql statement
				string strPreparedSQL = "INSERT INTO tblPlaylist (Filename, SortOrder) VALUES (@prmFilename, @prmSortOrder);";

				//prepare sql params 
				ArrayList arlSqlParams = new ArrayList();
				arlSqlParams.Add(new SqlParameter() { Name = "@prmFilename", Type = DbType.String, Value = Filename });
				arlSqlParams.Add(new SqlParameter() { Name = "@prmSortOrder", Type = DbType.Int64, Value = lngNewSortOrder });

				//fire statement (TODO _addFilenameToPlaylist check if affected row count can be used for verification purposes
				int intAffectedRowCount = _sh.ExecutePreparedStatement(strPreparedSQL, arlSqlParams);
			}
		}

		private void _deleteItemsFromPlaylist(
            IList lstSelectedItems)
		{
            foreach (PlaylistItem pli in lstSelectedItems)
            {
                //declare string to hold the prepared sql statement
                string strPreparedSQL = "DELETE FROM tblPlaylist WHERE id = @prmId;";

                //prepare sql params 
                ArrayList arlSqlParams = new ArrayList();
                arlSqlParams.Add(new SqlParameter() { Name = "@prmId", Type = DbType.Int64, Value = pli.Id });

                //fire statement (TODO _addFilenameToPlaylist check if affected row count can be used for verification purposes
                int intAffectedRowCount = _sh.ExecutePreparedStatement(strPreparedSQL, arlSqlParams);
            }
		}

		/// <summary>
		/// read playlist in ascending order
		/// </summary>
		private void _readPlaylist()
		{
			_readPlaylist(false);
		}

		/// <summary>
		/// read playlist in a chosen order
		/// </summary>
		/// <param name="descending">if true, the list will be read descending</param>
		private void _readPlaylist(
			bool descending)
        {
			//clear list if not empty
            if (_ocPlaylistItems.Count > 0)
            {
                _ocPlaylistItems.Clear();
            }

			//generate SQL string and get table form database
			string strSQL = string.Concat("SELECT * FROM tblPlaylist ORDER BY SortOrder ", descending ? "DESC" : "ASC");
            DataTable dt = _sh.GetDataTable(strSQL);

			//cycle through table and generate items
            foreach (DataRow dr in dt.Rows)
            {
                PlaylistItem pi = new PlaylistItem();
                if (PlaylistItem.TryParse(dr, out pi))
                {
                    _ocPlaylistItems.Add(pi);
                }
            }
        }

		/// <summary>
		/// updates the database entry with the last played item
		/// </summary>
		/// <param name="pli">item which item.id to be stored in entry</param>
		/// <param name="Position">position in total seconds to be stored in entry</param>
        private void _updateCurrentMedia(
            PlaylistItem pli,
            TimeSpan Position)
        {
			//declare string to hold the prepared sql statement
            string strPreparedSQL;

            if (Position.TotalSeconds == 0 &&
                !_sh.HasRows("SELECT * FROM tblLastPlayed LIMIT 1"))
            {
				//if no entry exists, an INSERT INTO has to be called
				strPreparedSQL = "INSERT INTO tblLastPlayed (PlaylistId, Position) VALUES(@PlaylistId, @Position)";
            }
            else
            {
				//other than that, just call an UPDATE (Note: Update does'nt have a WHERE clause on purpose, since there should only be one entry in the table)
                strPreparedSQL = "UPDATE tblLastPlayed SET Position = @Position, PlaylistId = @PlaylistId";
            }

			//prepare sql params 
            ArrayList arlSqlParams = new ArrayList();
            arlSqlParams.Add(new SqlParameter() { Name = "@Position", Type = DbType.Int64, Value = Convert.ToInt64(Position.TotalSeconds) });
            arlSqlParams.Add(new SqlParameter() { Name = "@PlaylistId", Type = DbType.Int64, Value = pli.Id });

			//fire statement (TODO _updateCurrentMedia check if affected row count can be used for verification purposes
            int intAffectedRowCount = _sh.ExecutePreparedStatement(strPreparedSQL, arlSqlParams);
        }

		/// <summary>
		/// read the last played item and its position from database entry
		/// </summary>
		/// <returns>a PlaylistItem containing the required data</returns>
        private PlaylistItem _getLastPlayedItemAndPosition()
        {
			//build select statement that holds all relevant information
			string strSQL = "SELECT tblPlaylist.id, tblPlaylist.Filename, tblPlaylist.SortOrder, tblLastPlayed.Position FROM tblLastPlayed INNER JOIN tblPlaylist ON tblLastPlayed.PlaylistId = tblPlaylist.id;";
            DataTable dt = _sh.GetDataTable(strSQL);

			//generate PlaylistItem
            PlaylistItem pli;
            if (PlaylistItem.TryParse(dt.Rows[0], out pli))
            {
                return pli;
            }

			//if something went wrong, just return null
            return null;
        }

		/// <summary>
		/// updates SortOrder of play list item in database
		/// </summary>
		/// <param name="pli"></param>
		private void _updateSortOrder(
			PlaylistItem pli)
		{
			//declare string to hold the prepared sql statement
			string strPreparedSQL = "UPDATE tblPlaylist SET SortOrder = @prmSortOrder WHERE id = @prmId;";

			//prepare sql params 
			ArrayList arlSqlParams = new ArrayList();
			arlSqlParams.Add(new SqlParameter() { Name = "@prmId", Type = DbType.Int64, Value = pli.Id });
			arlSqlParams.Add(new SqlParameter() { Name = "@prmSortOrder", Type = DbType.Int64, Value = pli.SortOrder });

			//fire statement (TODO _updateSortOrder check if affected row count can be used for verification purposes
			int intAffectedRowCount = _sh.ExecutePreparedStatement(strPreparedSQL, arlSqlParams);
		}

		/// <summary>
		/// gets the highest SortOrder there is in db and returns adding 1
		/// </summary>
		/// <returns></returns>
        private long _getNewSortOrder()
        {
            if (!_sh.HasRows("SELECT * FROM tblPlaylist")) return 1;

            string strSQL = "SELECT max(SortOrder) as MaxOrder FROM tblPlaylist";
            DataTable dt = _sh.GetDataTable(strSQL);
            DataRow dr = dt.Rows[0];
            return ((long)dr["MaxOrder"]) + 1;
        }

		private bool _filenameExists(
			string Filename)
		{
			//declare string to hold the prepared sql statement
			string strPreparedSQL = "SELECT * FROM tblPlaylist WHERE Filename = @prmFilename";

			//prepare sql params 
			ArrayList arlSqlParams = new ArrayList();
			arlSqlParams.Add(new SqlParameter() { Name = "@prmFilename", Type = DbType.String, Value = Filename });

			return _sh.HasRows(strPreparedSQL, arlSqlParams);
		}
		#endregion



        #region methods
		/// <summary>
		/// read playlist in ascending order
		/// </summary>
		public void ReadPlaylist()
        {
            _readPlaylist();
        }
		/// <summary>
		/// read playlist in a chosen order
		/// </summary>
		/// <param name="descending">if true, the list will be read descending</param>
		public void ReadPlaylist(
			bool descending)
		{
			_readPlaylist(descending);
		}

		/// <summary>
		/// updates the database entry with the last played item
		/// </summary>
		/// <param name="pli">item which item.id to be stored in entry</param>
		/// <param name="Position">position in total seconds to be stored in entry</param>
		public void UpdateCurrentMedia(
            PlaylistItem pli,
            TimeSpan Position)
        {
            _updateCurrentMedia(pli, Position);
        }

		/// <summary>
		/// updates SortOrder of play list item in database
		/// </summary>
		/// <param name="pli"></param>
		public void UpdateSortOrder(
			PlaylistItem pli)
		{
			_updateSortOrder(pli);
		}

		public void AddFilenamesToPlaylist(
			string[] Filenames)
		{
			_addFilenamesToPlaylist(Filenames);
			_readPlaylist();
		}

		public void DeleteItemsFromPlaylist(
            IList lstSelectedItems)
		{
            _deleteItemsFromPlaylist(lstSelectedItems);
		}
		#endregion



        #region properties
        public ObservableCollection<PlaylistItem> PlayListItems
        {
            get { return _ocPlaylistItems; }
            set { _ocPlaylistItems = value; }
        }

        public PlaylistItem GetLastPlayedItemAndPosition()
        {
            return _getLastPlayedItemAndPosition();
        }
        #endregion
    }
}
